# -*- coding: utf-8 -*-

import pymysql
# from scrapy.conf import settings
from .items import DDItem


class DdPipeline(object):
    def process_item(self, item, spider):
        # 连接数据库
        conn = pymysql.connect(host="47.98.103.205", port=3306, user="shuah", password='Jwlxz723230!', db="dangdang", charset="utf8")
        cur = conn.cursor()  # 用来获得python执行Mysql命令的方法,也就是我们所说的操作游标
        # print("mysql connect success")  # 测试语句，这在程序执行时非常有效的理解程序是否执行到这一步

        # 存储当当商品信息的逻辑
        if isinstance(item, DDItem):
            try:
                # comment_num = item["comment_num"]
                DDid = item["DDid"]
                ISBN = item["ISBN"]
                name = item["name"]
                author = item["author"]
                press = item["press"]
                publishingTime = item["publishingTime"]
                typeName = item["typeName"]
                typeRank = item["typeRank"]
                commentNumber = item["commentNumber"]
                price = item["price"]
                selling = item["selling"]
                shipper = item["shipper"]
                openBook = item["openBook"]
                page = item["page"]
                pcking = item["pcking"]
                isSuit = item["isSuit"]
                classification = item["classification"]
                pic1 = item["pic1"]
                pic2 = item["pic2"]
                remd1 = item["remd1"]
                remd2 = item["remd2"]
                content1 = item["content1"]
                content2 = item["content2"]
                link = item["link"]
                categoryPath = item["categoryPath"]

                sql = "INSERT INTO dangdangBook(DDid,ISBN,name,author,press,publishingTime,typeName,typeRank,commentNumber,price,selling,shipper,openBook,page,pcking,isSuit,classification,pic1,pic2,remd1,remd2,content1,content2,link,categoryPath) VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')" % (DDid,ISBN,name,author,press,publishingTime,typeName,typeRank,commentNumber,price,selling,shipper,openBook,page,pcking,isSuit,classification,pic1,pic2,remd1,remd2,content1,content2,link,categoryPath)
                # print(sql)
                '''########################################################
                                        执行sql语句，将商品信息存入goods数据表
                            ########################################################'''
                try:
                    cur.execute(sql)  # 真正执行MySQL语句，即查询TABLE_PARAMS表的数据
                except Exception as err:
                    print(err)
                    conn.rollback()  # 事务回滚,为了保证数据的有效性将数据恢复到本次操作之前的状态.有时候会存在一个事务包含多个操作，而多个操作又都有顺序，顺序执行操作时，有一个执行失败，则之前操作成功的也会回滚，即未操作的状态
                else:
                    conn.commit()  # 当没有发生异常时，提交事务，避免出现一些不必要的错误
                    print("insert goods success")  # 测试语句

            except Exception as err:
                print(err, '    EORR1111!!!')



        conn.close()  # 关闭连接
        return item  # 框架要求返回一个item对象


#  创建的表 dangdangBook
#
# CREATE TABLE dangdangBook(
# id INT(13) AUTO_INCREMENT PRIMARY KEY,
# ISBN VARCHAR(255),
# name VARCHAR(255),
# author VARCHAR(255),
# press VARCHAR(255),
# publishingTime VARCHAR(255),
# typeName VARCHAR(255),
# typeRank VARCHAR(255),
# commentNumber VARCHAR(255),
# price VARCHAR(255),
# selling VARCHAR(255),
# shipper VARCHAR(255),
# openBook VARCHAR(255),
# page VARCHAR(255),
# pcking VARCHAR(255),
# isSuit VARCHAR(255),
# classification VARCHAR(255),
# pic1 VARCHAR(255),
# pic2 VARCHAR(255),
# remd1 VARCHAR(255),
# remd2 VARCHAR(255),
# content1 VARCHAR(255),
# content2 VARCHAR(255),
# link VARCHAR(255)
# )
